package org.warehouse.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import org.warehouse.dao.IDao;
import org.warehouse.dao.IGroupDao;
import org.warehouse.dao.engine.DaoException;
import org.warehouse.entity.Group;
import org.warehouse.entity.Product;
import org.warehouse.util.Sort;

public class GroupDao extends AbstractDao implements IGroupDao {

	private static IGroupDao instance;

	public static IGroupDao getInstance() {
		if (instance == null) {
			synchronized (GroupDao.class) {
				if (instance == null) {
					instance = new GroupDao();
				}
			}
		}
		return instance;
	}
	
	private GroupDao() {
		super();
	}

	@Override
	public String getTableName() {
		return "T_GROUP";
	}

	public String getIndexColumnName() {
		return "GROUP_ID";
	}

	@Override
	public Integer put(Group entity) {
		String sql = MessageFormat.format("INSERT INTO {0} VALUES (?, ?)", getTableName());
		PreparedStatement ps = getDbExecutor().getPreparedStatement(sql);
		Integer index = getNewIndex();
		try {
			ps.setInt(1, index);
			ps.setString(2, entity.getName());
			getDbExecutor().invokeUpdate(ps);
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			getDbExecutor().closePreparedStatement(ps);
		}

		return index;
	}

	@Override
	public List<Group> getAll() {
		String sql = "SELECT * FROM " + getTableName();
		getLogger().info("Get all groups, SQL: " + sql);
		PreparedStatement ps = getDbExecutor().getPreparedStatement(sql);
		ResultSet rs = getDbExecutor().invoke(ps);

		List<Group> groups = new ArrayList<Group>();
		try {
			while (rs.next()) {
				Integer groupId = rs.getInt(getIndexColumnName());
				
				// TODO should remade this login for using one request 
				Integer countOfProducts = getCountOfProducts(groupId);
				
				Group group = new Group(groupId, rs.getString("GROUP_NAME"), countOfProducts);
				groups.add(group);
			}
			return groups;
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			getDbExecutor().closePreparedStatement(ps);
		}
	}

	@Override
	public List<Product> getProducts(Integer groupId) {
		IDao productDao = ProductDao.getInstance();

		String sql = MessageFormat.format("SELECT * FROM {0} WHERE {1} = ?", productDao.getTableName(), getIndexColumnName());
		getLogger().info("Get products by group id, SQL: " + sql);
		PreparedStatement ps = null;
		try {
			ps = getDbExecutor().getPreparedStatement(sql);
			ps.setInt(1, groupId);
			ResultSet rs = getDbExecutor().invoke(ps);

			List<Product> products = new ArrayList<Product>();

			while (rs.next()) {
				Product product = new Product(rs.getInt(productDao.getIndexColumnName()), rs.getString("PRODUCT_NAME"),
				        rs.getInt("PRODUCT_PRICE"), rs.getInt(getIndexColumnName()));
				products.add(product);
			}

			return products;
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			getDbExecutor().closePreparedStatement(ps);
		}
	}
	
	@Override
	public List<Product> getProductsForPage(Integer groupId, Integer limit, Integer offset, Sort sortByName, Sort sortByPrice, String lastSortColumnName) {
		IDao productDao = ProductDao.getInstance();

		// Create main part of a sql request
		String mainSql = MessageFormat.format("SELECT * FROM {0} WHERE {1} = ? ", productDao.getTableName(), getIndexColumnName());

		// Create part of the sql request which responses for ordering
		String orderSql;
		if (lastSortColumnName.equals("PRODUCT_NAME")) {
			orderSql = MessageFormat.format("ORDER BY PRODUCT_NAME {0}, PRODUCT_PRICE {1} ", sortByName.getSort(), sortByPrice.getSort());
		} else if (lastSortColumnName.equals("PRODUCT_PRICE")) {
			orderSql = MessageFormat.format("ORDER BY PRODUCT_PRICE {0}, PRODUCT_NAME {1} ", sortByPrice.getSort(), sortByName.getSort());
		} else {
			throw new DaoException("The column " + lastSortColumnName + " doesn't exist");
		}

		// Create part of the sql request which responses for paging
		String pagingSql = "LIMIT ? OFFSET ?";

		String sql =mainSql + orderSql + pagingSql;
		getLogger().info("Get products by group id, SQL: " + sql);

		PreparedStatement ps = null;
		try {
			ps = getDbExecutor().getPreparedStatement(sql);
			ps.setInt(1, groupId);
			ps.setInt(2, limit);
			ps.setInt(3, offset);
			ResultSet rs = getDbExecutor().invoke(ps);

			List<Product> products = new ArrayList<Product>();

			while (rs.next()) {
				Product product = new Product(rs.getInt(productDao.getIndexColumnName()), rs.getString("PRODUCT_NAME"),
				        rs.getInt("PRODUCT_PRICE"), rs.getInt(getIndexColumnName()));
				products.add(product);
			}

			return products;
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			getDbExecutor().closePreparedStatement(ps);
		}
	}
	
	@Override
	public int getCountOfProducts(Integer groupId) {
		IDao productDao = ProductDao.getInstance();

		String sql = MessageFormat.format("SELECT COUNT({0}) FROM {1} WHERE {2} = ?", productDao.getIndexColumnName(),
		        productDao.getTableName(), getIndexColumnName());
		
//		getLogger().info("Get count of products by group id, SQL: " + sql);

		PreparedStatement ps = null;
		try {
			ps = getDbExecutor().getPreparedStatement(sql);
			ps.setInt(1, groupId);
			ResultSet rs = getDbExecutor().invoke(ps);

			if (!rs.next()) {
	        	return 0;
	        } else {
	        	return rs.getInt(MessageFormat.format("COUNT({0})", productDao.getIndexColumnName()));
	        }
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			getDbExecutor().closePreparedStatement(ps);
		}
	}

}
